#write in 2019,6,2
#write by lizhuoying

from sqlalchemy import create_engine,Column,Integer,String,DateTime,ForeignKey
import time
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship,backref
from datetime import datetime

DB_URI='mysql+pymysql://root@127.0.0.1:3306/test?charset=utf8'

engine=create_engine(DB_URI)

Base=declarative_base(engine)#元类编程，将对象转化成元类

class Author(Base):
    __tablename__='author'
    id=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(20))


class Book(Base):
    __tablename__='book'
    id=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(20))
    create_time=Column(DateTime,default=datetime.now)
    author_id=Column(Integer,ForeignKey("author.id"))
    author=relationship('Author',backref=backref('Book',order_by=create_time.desc()))
    # __mapper_args__={
    #     "order_by":create_time.desc()
    # }
    def __repr__(self):
        return '<Book %s,%s>'%(self.create_time,self.name)

Base.metadata.drop_all()
Base.metadata.create_all()
session=sessionmaker(engine)()

book1=Book(name='book1')

book2=Book(name='book2')

session.add(book1)

session.commit()

time.sleep(3)

session.add(book2)

session.commit()

result=session.query(Book).order_by(Book.create_time)

print(result)